﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Xml;
using System.IO;

using Microsoft.AnalysisServices.AdomdClient;

using Ko2.DB.BI;

namespace Ko2.DB.BI.KPI
{

    public class Top10Shops : MXBase
    {

        public Exception Get(ref DataTable tbl)
        {
            string ConnStr = _env.GetAppSetting("BIConnect");
            DateTime dt = DateTime.Now;
            int Year = dt.Year;
            int Month = dt.Month;
            return Get(ref tbl, ConnStr, Year, Month);
        }

        public Exception Get(ref DataTable tbl, int parYear, int parMonth)
        {
            string ConnStr = _env.GetAppSetting("BIConnect");
            return Get(ref tbl, ConnStr, parYear, parMonth);
        }

        public Exception Get(ref DataTable tbl, string parConnStr, int parYear, int parMonth)
        {
            #region Members

            Exception ret = null;
            string MonthStr = MakeDT_Y_M(parYear, parMonth);

            #region SQL
            /*
SELECT
{
	[Measures].[Оборот Все Руб]
	,[Measures].[План на месяц]
	,[Measures].[Прогноз7 руб]
	,[Measures].[Откл-е прогноза %]
}
DIMENSION PROPERTIES PARENT_UNIQUE_NAME
,HIERARCHY_UNIQUE_NAME ON COLUMNS 
, NON EMPTY 
Hierarchize(
{
	DrilldownLevel(
	{
	[Подразделение].[Подразделение].[Все]}
	,,,
	INCLUDE_CALC_MEMBERS)
	}) 
DIMENSION PROPERTIES PARENT_UNIQUE_NAME
,HIERARCHY_UNIQUE_NAME
,[Подразделение].[Подразделение].[Подразделение].[Регионы]
,[Подразделение].[Подразделение].[Подразделение].[Тип] 
ON ROWS 
FROM
(
	SELECT 
		Generate(Hierarchize({
			[Подразделение].[Подразделение].[Все]}) AS [XL_Filter_Set_0]
			, TopCount(Filter(Except(DrilldownLevel([XL_Filter_Set_0].Current AS [XL_Filter_HelperSet_0]
			, , 0,INCLUDE_CALC_MEMBERS)
			, [XL_Filter_HelperSet_0])
			, Not IsEmpty([Measures].[Оборот Все Руб]))
			, 10
			, [Measures].[Оборот Все Руб])) 
	ON COLUMNS 
	FROM [KPI] 
	WHERE (
		[Дата].[Г-М].[Месяц].&[201112])) 
WHERE ([Дата].[Г-М].[Месяц].&[201112]) 
CELL PROPERTIES VALUE
, FORMAT_STRING
, LANGUAGE
, BACK_COLOR
, FORE_COLOR
, FONT_FLAGS
            */
            string cmdStr =
    @"
SELECT
{
	[Measures].[Оборот Все Руб]
	,[Measures].[План на месяц]
	,[Measures].[Прогноз7 руб]
	,[Measures].[Откл-е прогноза %]
}
DIMENSION PROPERTIES PARENT_UNIQUE_NAME
,HIERARCHY_UNIQUE_NAME ON COLUMNS 
, NON EMPTY 
Hierarchize(
{
	DrilldownLevel(
	{
	[Подразделение].[Подразделение].[Все]}
	,,,
	INCLUDE_CALC_MEMBERS)
	}) 
DIMENSION PROPERTIES PARENT_UNIQUE_NAME
,HIERARCHY_UNIQUE_NAME
,[Подразделение].[Подразделение].[Подразделение].[Регионы]
,[Подразделение].[Подразделение].[Подразделение].[Тип] 
ON ROWS 
FROM
(
	SELECT 
		Generate(Hierarchize({
			[Подразделение].[Подразделение].[Все]}) AS [XL_Filter_Set_0]
			, TopCount(Filter(Except(DrilldownLevel([XL_Filter_Set_0].Current AS [XL_Filter_HelperSet_0]
			, , 0,INCLUDE_CALC_MEMBERS)
			, [XL_Filter_HelperSet_0])
			, Not IsEmpty([Measures].[Оборот Все Руб]))
			, 10
			, [Measures].[Оборот Все Руб])) 
	ON COLUMNS 
	FROM [KPI] 
	WHERE (
		[Дата].[Г-М].[Месяц].&[" + MonthStr + @"])) 
WHERE ([Дата].[Г-М].[Месяц].&[" + MonthStr + @"]) 
CELL PROPERTIES VALUE
, FORMAT_STRING
, LANGUAGE
, BACK_COLOR
, FORE_COLOR
, FONT_FLAGS
";

            #endregion SQL

            #endregion Members

            CellSet cst = null;
            Exception err = Exec(ref cst, parConnStr, cmdStr);

            if (err != null)
                ret = new Exception(err.Message);
            else
            {
                err = Parse_001(ref cst, ref tbl);
                if (err != null)
                    ret = new Exception(err.Message);
            }

            return ret;
        }

        private Exception Parse_001(ref CellSet parCst, ref DataTable parTbl)
        {
            Exception ret = null;
            MakeTbl_001(ref parTbl);

            #region Init

            // check if any axes were returned else throw error.
            int axes_count = parCst.Axes.Count;
            if (axes_count == 0)
                return new Exception("No data returned for the selection");

            // if axes count is not 2
            if (axes_count != 2)
                return new Exception("The sample code support only queries with two axes");

            // if no position on either row or column throw error
            if (!(parCst.Axes[0].Positions.Count > 0) && !(parCst.Axes[1].Positions.Count > 0))
                return new Exception("No data returned for the selection");

            #endregion

            #region Members

            int cur_row, cur_col, col_count, row_count, col_dim_count, row_dim_count;
            cur_row = cur_col = col_count = row_count = col_dim_count = row_dim_count = 0;

            // Number of dimensions on the column
            col_dim_count = parCst.Axes[0].Positions[0].Members.Count;

            // Number of dimensions on the row
            if (parCst.Axes[1].Positions[0].Members.Count > 0)
                row_dim_count = parCst.Axes[1].Positions[0].Members.Count;

            // Total rows and columns
            row_count = parCst.Axes[1].Positions.Count + col_dim_count;  //number of rows + rows for column headers
            col_count = parCst.Axes[0].Positions.Count + row_dim_count;  //number of columns + columns for row headers

            int count = 0;
            DataRow r = null;

            #endregion

            #region for

            for (cur_row = 0; cur_row < row_count - 1; cur_row++)
            {

                count++;
                if (count >= 1)
                    r = parTbl.NewRow();
                else
                    continue;

                string Name = parCst.Axes[1].Positions[cur_row].Members[0].Caption;
                //string strValueType = parCst.Axes[1].Positions[cur_row].Members[1].Caption;


                #region Data

                Decimal val1 = Convert.ToDecimal(parCst[0, cur_row].Value);
                Decimal val2 = Convert.ToDecimal(parCst[1, cur_row].Value);
                Decimal val3 = Convert.ToDecimal(parCst[2, cur_row].Value);
                Decimal val4 = Convert.ToDecimal(parCst[3, cur_row].Value);

                r["Name"] = Name;
                r["Turnover"] = val1;
                r["Plan"] = val2;
                r["Forecast"] = val3;
                r["Deviation"] = val4;

                parTbl.Rows.Add(r);

                #endregion

            }

            #endregion

            return ret;
        }

        public void MakeTbl_001(ref DataTable parTbl)
        {
            if (parTbl == null)
                parTbl = new DataTable();

            parTbl.Columns.Add("Name", typeof(string));
            parTbl.Columns.Add("Turnover", typeof(Decimal));
            parTbl.Columns.Add("Plan", typeof(Decimal));
            parTbl.Columns.Add("Forecast", typeof(Decimal));
            parTbl.Columns.Add("Deviation", typeof(Decimal));
        }

    }
}
